{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8fc4cdc8-c7e9-4670-b87f-0d9fbf5b2abd",
   "metadata": {},
   "source": [
    "轴向堆叠数据\n",
    "concat（）函数可以沿着一条轴将多个对象进行堆叠，其使用方式类似数据库中的数据表合并。\n",
    "pandas.concat(objs,axis=0,join='outer',join_axes=None,ignore_index=False,keys=None,levels=None,\n",
    "              verify_interity=False,sort=None,copy=True)\n",
    "axis：表示连接的轴向，可以为0或1，默认为0（纵向堆叠）\n",
    "join：表示连接的方式，inner表示内连接，outer表示外连接，默认使用外连接\n",
    "ignore_index：接收布尔值，默认值为False。如果设置为True，则表示清除现有索引并重置索引值\n",
    "keys：接收序列，表示添加最外层索引\n",
    "levels：用于构建multiindex的特定级别（唯一值）\n",
    "names：在设置keys和level参数后，用于创建分层级别的名称\n",
    "verify_integerity：检查新的连接轴是否包含重复项。接收布尔值，当设置为true时，如果有重复的轴将会抛出错误，默认为false"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "31ae7a64-0b8a-4f94-9f52-fcb898f0045b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B   C   D\n",
      "0   A0   B0  C0  D0\n",
      "1   A1   B0  C0  D2\n",
      "2   A2   B2  C1  D2\n",
      "3  NaN  NaN  C3  D3\n"
     ]
    }
   ],
   "source": [
    "# 横向堆叠与外连接\n",
    "import pandas as pd\n",
    "df1 = pd.DataFrame({'A':['A0','A1','A2'],\n",
    "                   'B':['B0','B0','B2']})\n",
    "df2 = pd.DataFrame({'C':['C0','C0','C1','C3'],\n",
    "                   'D':['D0','D2','D2','D3']})\n",
    "# 横向堆叠（axis=1）合并df1和df2，采用外连接\n",
    "print(pd.concat([df1,df2],join='outer',axis=1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a31f1a88-796e-451e-a1ec-ad9eae5ee5c5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    B   C\n",
      "0  B0  C0\n",
      "1  B1  C1\n",
      "2  B2  C2\n",
      "0  B3  C3\n",
      "1  B4  C4\n",
      "2  B5  C5\n"
     ]
    }
   ],
   "source": [
    "# 纵向堆叠与内连接\n",
    "import pandas as pd\n",
    "df1 = pd.DataFrame({'A':['A0','A1','A2'],\n",
    "                   'B':['B0','B1','B2'],\n",
    "                   'C':['C0','C1','C2']})\n",
    "df2 = pd.DataFrame({'B':['B3','B4','B5'],\n",
    "                   'C':['C3','C4','C5'],\n",
    "                   'D':['D3','D4','D5']})\n",
    "print(pd.concat([df1,df2],join='inner',axis=0))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "342cd4b9-6446-4a15-a2e3-13b294413c19",
   "metadata": {},
   "source": [
    "主键合并类似于关系型数据库的连接方式，它是指根据一个或多个键将不同的\r\n",
    "DataFrame 对象连接起来，大多数是将两个 DataFrame 对象中重叠的列作为合\r\n",
    "并的键。Pandas中提供了用于主键合并的merge()函数，其语法格式如\n",
    "pandas.merge(left,right,how='inner',on=None,left_on=None,right\r\n",
    "_on=None,left_index=False,right_index=False,sort=False,suffixe\r\n",
    "s=('_x','_y'),copy=True,indicator=False,validate=Non\n",
    "(1)left:参与合并的左侧DataFrame对象\r\n",
    "(2)right:参与合并的右侧DataFrame对象\r\n",
    "(3)how:表示连接方式，默认为inner,该参数支持以下取值\r\n",
    "◆left:使用左侧的DataFrame的键，类似SQL的左外连接\r\n",
    "◆right:使用右侧的DataFrame的键，类似SQL的右外连接\r\n",
    "◆outer:使用两个DataFrame所有的键，类似SQL的全连接\r\n",
    "◆inner:使用两个DataFrame键的交集，类似SQL的内连接\r\n",
    "(4)on:用于连接的列名。必须存在于左右两个DataFrame对象中\r\n",
    "(5)left_on:以左侧DataFrame做为连接键\r\n",
    "(6)right_on:以右侧DataFrame做为连接键\r\n",
    "(7)left_index:左侧的行索引用作连接键\r\n",
    "(8)right_index:右侧的行索引用作连接键\r\n",
    "(9)sort:是否排序，接收布尔值，默认为False\r\n",
    "(10)suffixes:用于追加到重叠列名的末尾，默认为(_x,_y)\r\n",
    "在使用merge()函数进行合并时，默认会使用重叠的列索引做为合并键，并采用\r\n",
    "内连接方式合并数据，即取行索引重叠的部分。\r\n",
    "merge()函数的典型应用场景是，针对同一个主键存在两张不同字段的表，根据\r\n",
    "主键整合到一张表里面。例如，图4-13中的left表和right表中均有索引\r\n",
    "为“key”的一列数据，并且这一列有重叠的数据，这两张表进行主键合并后得到\r\n",
    "result表e)下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "189647d6-6442-4d90-b478-dac2e7231f90",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key   A   B   C   D\n",
      "0  K0  A0  B0  C0  D0\n",
      "1  K1  A1  B1  C1  D1\n",
      "2  K2  A2  B2  C2  D2\n"
     ]
    }
   ],
   "source": [
    "# 使用merge函数将left表与right表进行合并\n",
    "import pandas as pd\n",
    "left = pd.DataFrame({'key':['K0','K1','K2'],\n",
    "                    'A':['A0','A1','A2'],\n",
    "                    'B':['B0','B1','B2']})\n",
    "right = pd.DataFrame({'key':['K0','K1','K2','K3'],\n",
    "                    'C':['C0','C1','C2','C3'],\n",
    "                    'D':['D0','D1','D2','D3']})\n",
    "print(pd.merge(left,right,on='key'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "da1d3c7d-9975-4e8d-82f6-5e0bdb21845d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key   A   B   C   D\n",
      "0  K0  A0  B0  C0  D0\n",
      "1  K2  A2  B2  C2  D2\n"
     ]
    }
   ],
   "source": [
    "# 含有多个重叠列的dataframe对象进行合并\n",
    "import pandas as pd\n",
    "left = pd.DataFrame({'key':['K0','K1','K2'],\n",
    "                    'A':['A0','A1','A2'],\n",
    "                    'B':['B0','B1','B2']})\n",
    "right = pd.DataFrame({'key':['K0','K5','K2','K4'],\n",
    "                      'B':['B0','B1','B2','B3'],\n",
    "                    'C':['C0','C1','C2','C3'],\n",
    "                    'D':['D0','D1','D2','D3']})\n",
    "print(pd.merge(left,right,on=['key','B']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "91e634ad-d6b6-484f-87f8-4fd991ccaf45",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B    C    D\n",
      "0   A0   B0  NaN  NaN\n",
      "1   A1   B0  NaN  NaN\n",
      "2   A2   B2  NaN  NaN\n",
      "a  NaN  NaN   C0   D0\n",
      "b  NaN  NaN   C0   D2\n",
      "c  NaN  NaN   C1   D2\n"
     ]
    }
   ],
   "source": [
    "# 两张表没有重叠部分只需要将参数left_index与right_index的值设置为True\n",
    "import pandas as pd\n",
    "left = pd.DataFrame({'A':['A0','A1','A2'],\n",
    "                   'B':['B0','B0','B2']},index=[0,1,2])\n",
    "right = pd.DataFrame({'C':['C0','C0','C1'],\n",
    "                   'D':['D0','D2','D2']},index=['a','b','c'])\n",
    "print(pd.merge(left,right,how='outer',left_index=True,right_index=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d22b40a3-9650-4f6b-af7e-2e1408c19614",
   "metadata": {},
   "source": [
    "join() 方法能够通过索引或指定列来连接 DataFrame ，其语法格式\n",
    "join（other，on = None，how ='left'，lsuffix =''，rsuffix =''，\r\n",
    "sort = False ）\r\n",
    "上述方法常用参数表示的含义：\r\n",
    "（1）on：用于连接列名\r\n",
    "（2）how：可以从['left', 'right', 'outer', 'inner']中任选一个，默\r\n",
    "认使用left的方式\r\n",
    "（3）lsuffix：接收字符串，用于在左侧重叠的列名后添加后缀名\r\n",
    "（4）rsuffix：接收字符串，用于在右侧重叠的列名后添加后缀名\r\n",
    "（5）sort：接收布尔值，根据连接键对合并的数据进行排序，默认为False\r\n",
    "之前提到，当两张表中如果没有重叠的部分，可以设置merge()函数的\r\n",
    "left_index和right_index参数，而对join()方法来说只需要将表后\r\n",
    "左表的数据会全部展示"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b368b0d8-381c-4391-94e5-5cf9f135fc74",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B    C    D\n",
      "a   A0   B0  NaN  NaN\n",
      "b   A1   B0  NaN  NaN\n",
      "c  NaN  NaN   C0   D0\n",
      "d  NaN  NaN   C0   D2\n"
     ]
    }
   ],
   "source": [
    "# join()方法默认使用左连接的方式，即以左表为基准，join()方法进行合并后左表的数据会全部展示\n",
    "import pandas as pd\n",
    "left = pd.DataFrame({'A':['A0','A1'],\n",
    "                   'B':['B0','B0']},index=['a','b'])\n",
    "right = pd.DataFrame({'C':['C0','C0'],\n",
    "                   'D':['D0','D2']},index=['c','d'])\n",
    "print(left.join(right,how='outer'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7e537acb-5dff-4f9f-815b-9cb4caae9ef6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B key   C   D\n",
      "0  A0  B0  k0  C0  D0\n",
      "1  A1  B0  k1  C1  D2\n",
      "2  A2  B2  k2  C1  D2\n"
     ]
    }
   ],
   "source": [
    "# 假设两个表中行索引和列索引重叠，那么当使用 join() 方法进行合并时，使用参数 on 指定重叠的列名即可\n",
    "import pandas as pd\n",
    "left = pd.DataFrame({'A':['A0','A1','A2'],\n",
    "                   'B':['B0','B0','B2'],\n",
    "                    'key':['k0','k1','k2']},index=[0,1,2])\n",
    "right = pd.DataFrame({'C':['C0','C1','C1'],\n",
    "                   'D':['D0','D2','D2']},index=['k0','k1','k2'])\n",
    "# on 参数指定连接的列名\n",
    "print(left.join(right,how='outer',on='key'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7ce2a4c-21f9-42ff-bba2-06123fd6499e",
   "metadata": {},
   "source": [
    "合并重叠数据 \r\n",
    "在处理数据的过程中，当一个 DatadFrame 对象中出现了缺失数据，而对于这些\r\n",
    "缺失数据，我们希望可以使用其他 DataFrame 对象中的数据填充，这时可以通\r\n",
    "过 combine_first() 方法填充缺失数据\r\n",
    "combine_first() 方法的语e\n",
    "# on 参数指定连接的列名\r\n",
    "left.join(right, how='left', on='key')\r\n",
    "combine_first(other)\r\n",
    "- other：该参数用于接收填充缺失的值的DataFrame对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5c400fb5-7df6-40b2-b2fe-d817e6cff329",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "      <td>k0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A0</td>\n",
       "      <td>B1</td>\n",
       "      <td>k1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A1</td>\n",
       "      <td>D2</td>\n",
       "      <td>k2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A2</td>\n",
       "      <td>B3</td>\n",
       "      <td>k3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B key\n",
       "0  C1  D1  k0\n",
       "1  A0  B1  k1\n",
       "2  A1  D2  k2\n",
       "3  A2  B3  k3"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from numpy import NAN\n",
    "left = pd.DataFrame({'A':[np.nan,'A0','A1','A2'],\n",
    "                    'B':[np.nan,'B1',np.nan,'B3'],\n",
    "                    'key':['k0','k1','k2','k3']},index=[0,1,2,3])\n",
    "right = pd.DataFrame({'A':['C0','C1','C2'],\n",
    "                     'B':['D0','D1','D2']},index=[1,0,2])\n",
    "\n",
    "# 用right的数据填充left缺失的部分\n",
    "left.combine_first(right)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
